﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Globalization;
using POSPlus.PrintManager;

namespace POSPlus.EZBarTender.DatabaseOperations
{
	/// <summary>
	/// Accesses the POSplus_PrintManagerSigns and does several operations with that data.
	/// </summary>
	public class SignsWithJobName : DatabaseObject, IComparable<SignsWithJobName>
	{
		#region Properties
		public string Upc { get; private set; }
		public string Description1 { get; private set; }
		public string Description2 { get; private set; }
		public string Description3 { get; private set; }
		public string SpecialPrice { get; private set; }
		public decimal NormalPrice { get; private set; }
		public double UnitSP { get; private set; }
		public string Size { get; private set; }
		public DateTime WeekEnding { get; private set; }
		public decimal YouSave { get; private set; }
		public string Description4 { get; private set; }
		public string Description5 { get; private set; }
		public string JobName { get; private set; }
		public string Type { get; private set; }
		public int Copies { get; private set; }
		public String BatchNo { get; private set; }
        public string Uom { get; private set; }
        public DateTime StartDate { get; private set; }
		#endregion

		#region Constructors
		/// <summary>
		/// Initializes a new instance of the POSPlus.EZBarTender.DatabaseOperation.PrintManagerSign class 
		/// when given a string that contains the connection string.
		/// </summary>
		/// <param name="connectionString"></param>
		public SignsWithJobName(String connectionString) : base(connectionString)
		{
			PopulateDefault();
		}

		/// <summary>
		/// Intialized a new instance of the POSPlus.EZBarTender.DatabaseOperation.PrintManagerSign class
		/// with the connection string, upc, and weekending.  It accesses the database and loads the results
		/// of the query with the obtained record.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="upc"></param>
		/// <param name="weekEnding"></param>
		public SignsWithJobName(String connectionString, String upc, String batchNo) : base(connectionString)
		{
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT Upc, Description1, Description2, Description3, " +
					"Description4, Description5, NormalPrice, UnitSP, SpecialPrice, Size, WeekEnding, YouSave, " +
					"JobName, Type, Copies, BatchNo, Uom, StartDate FROM POSplus_SignsWithJobName " +
					"WHERE Upc = @Upc AND BatchNo = @BatchNo", connection))
				{
					command.Parameters.AddWithValue("@Upc", upc);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						if (reader.Read())
						{
							Upc = FromDB<string>(reader["Upc"]);
							Description1 = FromDB<string>(reader["Description1"]);
							Description2 = FromDB<string>(reader["Description2"]);
							Description3 = FromDB<string>(reader["Description3"]);
							Description4 = FromDB<string>(reader["Description4"]);
							Description5 = FromDB<string>(reader["Description5"]);
							NormalPrice = FromDB<decimal>(reader["NormalPrice"]);
							UnitSP = FromDB<double>(reader["UnitSP"]);
							SpecialPrice = FromDB<string>(reader["SpecialPrice"]);
							Size = FromDB<string>(reader["Size"]);
							WeekEnding = FromDB<DateTime>(reader["WeekEnding"]);
							YouSave = FromDB<decimal>(reader["YouSave"]);
							JobName = FromDB<string>(reader["JobName"]);
							Type = FromDB<string>(reader["Type"]);
							Copies = FromDB<int>(reader["Copies"]);
							BatchNo = FromDB<string>(reader["BatchNo"]);
                            Uom = FromDB<string>(reader["Uom"]);
                            StartDate = FromDB<DateTime>(reader["StartDate"]);
						}
						else
						{
							PopulateDefault();
						}
						reader.Close();
						connection.Close();
					}
				}
			}
		}
		#endregion

		#region Methods
		/// <summary>
		/// Sets the properties to be the defaults for the database.
		/// </summary>
		public override void PopulateDefault()
		{
			Upc = "";
			Description1 = "";
			Description2 = "";
			Description3 = "";
			Description4 = "";
			Description5 = "";
			NormalPrice = default(decimal);
			UnitSP = default(double);
			SpecialPrice = "";
			Size = "";
			WeekEnding = new DateTime(1900, 1, 1);
			YouSave = default(decimal);
			JobName = "";
			Type = "";
			Copies = default(int);
			BatchNo = "";
            Uom = "";
            StartDate = new DateTime(1900, 1, 1);
		}

		/// <summary>
		/// Inserts the current object state into the SQL database.
		/// </summary>
		public override Boolean Create()
		{
			int rowsAffected = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("INSERT INTO POSplus_SignsWithJobName " +
					"(Upc, Description1, Description2, Description3, Description4, Description5, NormalPrice, " +
                    "UnitSP, SpecialPrice, Size, WeekEnding, YouSave, Type, JobName, Copies, BatchNo, Uom, StartDate) " +
					"VALUES (@Upc, @Description1, @Description2, @Description3, @Description4, @Description5, " +
					"@NormalPrice, @UnitSP, @SpecialPrice, @Size, @WeekEnding, @YouSave, " +
                    "@Type, @Copies, @JobName, @BatchNo, @Uom, @StartDate)", connection))
				{
					command.Parameters.AddWithValue("@Upc", Upc);
					command.Parameters.AddWithValue("@Description1", Description1);
					command.Parameters.AddWithValue("@Description2", Description2);
					command.Parameters.AddWithValue("@Description3", Description3);
					command.Parameters.AddWithValue("@Description4", Description4);
					command.Parameters.AddWithValue("@Description5", Description5);
					command.Parameters.AddWithValue("@NormalPrice", NormalPrice);
					command.Parameters.AddWithValue("@UnitSP", UnitSP);
					command.Parameters.AddWithValue("@SpecialPrice", SpecialPrice);
					command.Parameters.AddWithValue("@Size", Size);
					command.Parameters.AddWithValue("@WeekEnding", WeekEnding);
					command.Parameters.AddWithValue("@YouSave", YouSave);
					command.Parameters.AddWithValue("@Type", Type);
					command.Parameters.AddWithValue("@Copies", Copies);
					command.Parameters.AddWithValue("@JobName", JobName);
					command.Parameters.AddWithValue("@BatchNo", BatchNo);
                    command.Parameters.AddWithValue("@Uom", BatchNo);
                    command.Parameters.AddWithValue("@StartDate", StartDate);
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
				}
			}
			return rowsAffected > 0;
		}

		/// <summary>
		/// Updates the database record with the current object state.
		/// </summary>
		public override Boolean Update()
		{
			if (Upc == "") return false;
			int rowsAffected = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("UPDATE POSplus_SignsWithJobName SET " +
					"Upc = @Upc, Description1 = @Description1, Description2 = @Description2, " +
					"Description3 = @Description3, Description4 = @Description4, Description5 = @Description5, " +
					"NormalPrice = @NormalPrice, UnitSP = @UnitSP, SpecialPrice = @SpecialPrice, Size = @Size, " +
					"WeekEnding = @WeekEnding, YouSave = @YouSave, Type = @Type, Copies = @Copies, " +
                    "JobName = @JobName, BatchNo = @BatchNo, Uom = @Uom, @StartDate = StartDate WHERE Upc = @Upc AND JobName = @JobName AND BatchNo = @BatchNo ", connection))
				{
					command.Parameters.AddWithValue("@Upc", Upc);
					command.Parameters.AddWithValue("@Description1", Description1);
					command.Parameters.AddWithValue("@Description2", Description2);
					command.Parameters.AddWithValue("@Description3", Description3);
					command.Parameters.AddWithValue("@Description4", Description4);
					command.Parameters.AddWithValue("@Description5", Description5);
					command.Parameters.AddWithValue("@NormalPrice", NormalPrice);
					command.Parameters.AddWithValue("@UnitSP", UnitSP);
					command.Parameters.AddWithValue("@SpecialPrice", SpecialPrice);
					command.Parameters.AddWithValue("@Size", Size);
					command.Parameters.AddWithValue("@WeekEnding", WeekEnding);
					command.Parameters.AddWithValue("@YouSave", YouSave);
					command.Parameters.AddWithValue("@Type", Type);
					command.Parameters.AddWithValue("@Copies", Copies);
					command.Parameters.AddWithValue("@JobName", JobName);
					command.Parameters.AddWithValue("@BatchNo", BatchNo);
                    command.Parameters.AddWithValue("@Uom", Uom);
                    command.Parameters.AddWithValue("@StartDate", StartDate);
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
				}
			}
			return rowsAffected > 0;
		}

		/// <summary>
		/// Deletes the object from the database.
		/// </summary>
		public override Boolean Delete()
		{
			int rowsAffected = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("DELETE FROM POSplus_SignsWithJobName " +
					"WHERE Upc = @Upc AND WeekEnding = @WeekEnding AND BatchNo = @BatchNo", connection))
				{
					command.Parameters.AddWithValue("@Upc", Upc);
					command.Parameters.AddWithValue("@WeekEnding", WeekEnding);
					command.Parameters.AddWithValue("@BatchNo", BatchNo);
					rowsAffected = command.ExecuteNonQuery();
					connection.Close();
					Upc = "";
					WeekEnding = new DateTime(1900, 1, 1);
				}
				connection.Close();
			}
			return rowsAffected > 0;
		}

		/// <summary>
		/// Retrieves all records order by Job Name then Upc.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <returns></returns>
		public static List<SignsWithJobName> GetAllOrderedByJobNameUpc(String connectionString)
		{
			List<SignsWithJobName> result = new List<SignsWithJobName>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_PrintManagerSigns ORDER BY " +
					"JobName, Upc", connection))
				{
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							SignsWithJobName printManagerSign = new SignsWithJobName(connectionString);
							printManagerSign.Description1 = FromDB<String>(reader["Description1"]);
							printManagerSign.Description2 = FromDB<String>(reader["Description2"]);
							printManagerSign.Description3 = FromDB<String>(reader["Description3"]);
							printManagerSign.Description4 = FromDB<String>(reader["Description4"]);
							printManagerSign.Description5 = FromDB<String>(reader["Description5"]);
							printManagerSign.NormalPrice = FromDB<decimal>(reader["NormalPrice"]);
							printManagerSign.UnitSP = FromDB<double>(reader["PriceUom"]);
							printManagerSign.SpecialPrice = FromDB<String>(reader["Selling"]);
							printManagerSign.Size = FromDB<String>(reader["Size"]);
							printManagerSign.Upc = FromDB<String>(reader["Upc"]);
							printManagerSign.WeekEnding = FromDB<DateTime>(reader["WeekEnding"]);
							printManagerSign.YouSave = FromDB<decimal>(reader["YouSave"]);
							printManagerSign.BatchNo = FromDB<string>(reader["BatchNo"]);
                            printManagerSign.Uom = FromDB<string>(reader["Uom"]);
                            printManagerSign.StartDate = FromDB<DateTime>(reader["StartDate"]);
							result.Add(printManagerSign);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return result;
		}

		/// <summary>
		/// Gets all records with the given Job Name ordered by Upc.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="jobName"></param>
		/// <returns></returns>
		public static List<SignsWithJobName> GetJobNamedOrdered(String connectionString, String jobName)
		{
			List<SignsWithJobName> result = new List<SignsWithJobName>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_PrintManagerSigns WHERE JobName LIKE @JobName " +
					"ORDER BY JobName, Upc", connection))
				{
					command.Parameters.AddWithValue("@JobName", jobName);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{

							SignsWithJobName sign = new SignsWithJobName(connectionString);
							sign.Description1 = FromDB<string>(reader["Description1"]);
							sign.Description2 = FromDB<string>(reader["Description2"]);
							sign.Description3 = FromDB<String>(reader["Description3"]);
							sign.Description4 = FromDB<string>(reader["Description4"]);
							sign.Description5 = FromDB<string>(reader["Description5"]);
							sign.NormalPrice = FromDB<decimal>(reader["NormalPrice"]);
							sign.UnitSP = FromDB<double>(reader["UnitSP"]);
							sign.SpecialPrice = FromDB<string>(reader["SpecialPrice"]);
							sign.Size = FromDB<string>(reader["Size"]);
							sign.Upc = FromDB<string>(reader["Upc"]);
							sign.WeekEnding = FromDB<DateTime>(reader["WeekEnding"]);
							sign.YouSave = FromDB<decimal>(reader["YouSave"]);
							sign.BatchNo = FromDB<string>(reader["BatchNo"]);
                            sign.Uom = FromDB<string>(reader["Uom"]);
                            sign.StartDate = FromDB<DateTime>(reader["StartDate"]);
							result.Add(sign);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return result;
		}

		/// <summary>
		/// Gets all records.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <returns></returns>
		public static List<SignsWithJobName> GetAll(String connectionString)
		{
			List<SignsWithJobName> result = new List<SignsWithJobName>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_PrintManagerSigns", connection))
				{
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							SignsWithJobName sign = new SignsWithJobName(connectionString);
							sign.Description1 = FromDB<string>(reader["Description1"]);
							sign.Description2 = FromDB<string>(reader["Description2"]);
							sign.Description3 = FromDB<String>(reader["Description3"]);
							sign.Description4 = FromDB<string>(reader["Description4"]);
							sign.Description5 = FromDB<string>(reader["Description5"]);
							sign.NormalPrice = FromDB<decimal>(reader["NormalPrice"]);
							sign.UnitSP = FromDB<double>(reader["UnitSP"]);
							sign.SpecialPrice = FromDB<string>(reader["SpecialPrice"]);
							sign.Size = FromDB<string>(reader["Size"]);
							sign.Upc = FromDB<string>(reader["Upc"]);
							sign.WeekEnding = FromDB<DateTime>(reader["WeekEnding"]);
							sign.YouSave = FromDB<decimal>(reader["YouSave"]);
							sign.BatchNo = FromDB<string>(reader["BatchNo"]);
                            sign.Uom = FromDB<string>(reader["Uom"]);
							result.Add(sign);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return result;
		}

		public static List<SignsWithJobName> GetAllOnJobName(String connectionString, String jobName, String type)
		{
			List<SignsWithJobName> printData = new List<SignsWithJobName>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_SignsWithJobName " +
					"WHERE JobName = @JobName AND Type = @Type", connection))
				{
					command.Parameters.AddWithValue("@JobName", jobName);
					command.Parameters.AddWithValue("@Type", type);

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							SignsWithJobName data = new SignsWithJobName(connectionString);
							data.Description1 = FromDB<String>(reader["Description1"]);
							data.Description2 = FromDB<String>(reader["Description2"]);
							data.Description3 = FromDB<String>(reader["Description3"]);
							data.Description4 = FromDB<String>(reader["Description4"]);
							data.Description5 = FromDB<String>(reader["Description5"]);
							data.NormalPrice = FromDB<decimal>(reader["NormalPrice"]);
							data.UnitSP = FromDB<Double>(reader["UnitSP"]);
							data.SpecialPrice = FromDB<String>(reader["SpecialPrice"]);
							data.Size = FromDB<String>(reader["Size"]);
							data.Upc = FromDB<String>(reader["Upc"]);
							data.WeekEnding = FromDB<DateTime>(reader["WeekEnding"]);
							data.YouSave = FromDB<decimal>(reader["YouSave"]);
							data.Type = FromDB<string>(reader["Type"]);
							data.Copies = FromDB<int>(reader["Copies"]);
							data.JobName = FromDB<string>(reader["JobName"]);
							data.BatchNo = FromDB<string>(reader["BatchNo"]);
                            data.Uom = FromDB<string>(reader["Uom"]);
                            data.StartDate = FromDB<DateTime>(reader["StartDate"]);
							printData.Add(data);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return printData;
		}

		private String MoneyConversion(double value)
		{
			if (value < 1)
				return Math.Round(value * 100, 0).ToString() + "\u00A2";
			else
			{
				return value.ToString("0.00");
			}
		}
		#endregion

		public int CompareTo(SignsWithJobName other)
		{
			return Upc.CompareTo(other.Upc);
		}
	}
}